﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BiffenService.Model;
using System.Data.SqlClient;
using System.Data;
using System.ServiceModel;
using System.Diagnostics;

namespace BiffenService.Db
{
    public class MovieDb : IMovieDb
    {
        public MovieDb() { }
        private string tableMovie = "bif_movie";

        /// <summary>
        ///     Get a single movie given an ID.
        /// </summary>
        /// <param name="id">ID of the movie.</param>
        /// <returns>A single movie.</returns>
        public Movie GetMovie(int id)
        {
            try
            {
                string sql = "";
                SqlCommand cmd = null;


                if (id < 0)
                {
                    sql = "SELECT TOP 1 * FROM bif_movie M, bif_price_category P WHERE M.PriceCategory=P.Id ORDER BY M.Id DESC";
                    cmd = DbClient.GetDbCommand(sql);
                }
                else
                {
                    sql = "SELECT * FROM bif_movie M, bif_price_category P WHERE M.PriceCategory=P.Id AND M.Id=@AddId";
                    cmd = DbClient.GetDbCommand(sql);
                    cmd.Parameters.AddWithValue("@AddId", id.ToString());
                }
                SqlDataReader r = cmd.ExecuteReader();

                Movie m = null;
                while (r.Read())
                {
                    string title = r["Title"].ToString();
                    int duration = int.Parse(r["Duration"].ToString());
                    int year = int.Parse(r["Year"].ToString());
                    string actors = r["Actors"].ToString();
                    string directors = r["Directors"].ToString();
                    PriceCategory price = new PriceCategory((int)r["PriceCategory"], (string)r["Name"], (decimal)r["Price"]);
                    string summary = r["Summary"].ToString();

                    m = new Movie(id, title, duration, year, actors, directors, price, summary);
                }
                r.Close();
                DbClient.Close();
                return m;
            }
            catch (Exception ex)
            {
                return null;
            }

        }

        /// <summary>
        ///     Get last added movie.
        /// </summary>
        /// <returns>A single movie.</returns>
        public Movie GetMovie()
        {
            return GetMovie(-1);
        }

        /// <summary>
        ///     Get all movies.
        /// </summary>
        /// <returns>A generic list of movies.</returns>
        public List<Movie> GetAllMovies()
        {
            try
            {
                string sql = "SELECT * FROM bif_movie M, bif_price_category P WHERE M.PriceCategory=P.Id ORDER BY M.Id ASC";
                SqlCommand cmd = DbClient.GetDbCommand(sql);
                SqlDataReader r = cmd.ExecuteReader();

                List<Movie> movies = new List<Movie>();

                while (r.Read())
                {
                    int id = int.Parse(r["Id"].ToString());
                    string title = r["Title"].ToString();
                    int duration = int.Parse(r["Duration"].ToString());
                    int year = int.Parse(r["Year"].ToString());
                    string actors = r["Actors"].ToString();
                    string directors = r["Directors"].ToString();
                    PriceCategory price = new PriceCategory((int)r["PriceCategory"], (string)r["Name"], (decimal)r["Price"]);
                    string summary = r["Summary"].ToString();

                    movies.Add(new Movie(id, title, duration, year, actors, directors, price, summary));
                }

                r.Close();
                DbClient.Close();
                if (movies.Count < 1)
                {
                    return null;
                }
                return movies;
            }
            catch (SqlException ex)
            {
                Debug.WriteLine(ex.Message);
                return null;
            }
        }

        /// <summary>
        ///     Add a single movie.
        /// </summary>
        /// <param name="m">A movie object.</param>
        /// <returns>The number of database rows affected.</returns>
        public int AddMovie(Movie m)
        {
            try
            {
                string sql = "INSERT INTO " + tableMovie + " (Title, Duration, Year, Actors, Directors, PriceCategory) VALUES (@Title, @Duration, @Year, @Actors, @Directors, @PriceCategory)";
                SqlCommand cmd = DbClient.GetDbCommand(sql);

                cmd.Parameters.AddWithValue("@Title", m.Title);
                cmd.Parameters.AddWithValue("@Year", m.Year);
                cmd.Parameters.AddWithValue("@Duration", m.Duration);
                cmd.Parameters.AddWithValue("@Actors", m.Actors);
                cmd.Parameters.AddWithValue("@Directors", m.Directors);
                cmd.Parameters.AddWithValue("@PriceCategory", m.PriceCategory.Id);

                int result = cmd.ExecuteNonQuery();

                DbClient.Close();

                return result;
            }
            catch (Exception)
            {
                return -1;
            }            
        }

        /// <summary>
        ///     Edit a single movie.
        /// </summary>
        /// <param name="m">A movie object, containing the updated data.</param>
        /// <returns>The number of database rows affected.</returns>
        public int EditMovie(Movie m)
        {
            try
            {
                string sql = "UPDATE " + tableMovie + " SET Title = @Title, Year = @Year, Duration = @Duration, Actors = @Actors, Directors = @Directors, PriceCategory = @Price WHERE Id = @Id";
                SqlCommand cmd = DbClient.GetDbCommand(sql);

                cmd.Parameters.AddWithValue("@Id", m.Id);
                cmd.Parameters.AddWithValue("@Title", m.Title);
                cmd.Parameters.AddWithValue("@Year", m.Year);
                cmd.Parameters.AddWithValue("@Duration", m.Duration);
                cmd.Parameters.AddWithValue("@Actors", m.Actors);
                cmd.Parameters.AddWithValue("@Directors", m.Directors);
                cmd.Parameters.AddWithValue("@Price", m.PriceCategory.Id);

                int result = cmd.ExecuteNonQuery();

                DbClient.Close();

                return result;
            }
            catch (Exception)
            {
                return -1;
            }
        }

        /// <summary>
        ///     Delete a single movie.
        /// </summary>
        /// <param name="id">The ID of the movie to delete.</param>
        /// <returns>The number of database rows affected.</returns>
        public int DeleteMovie(int id)
        {
            try
            {
                string sql = "DELETE FROM " + tableMovie + " WHERE Id=@DeleteId";
                SqlCommand cmd = DbClient.GetDbCommand(sql);
                cmd.Parameters.AddWithValue("@DeleteId", id);

                int result = cmd.ExecuteNonQuery();

                DbClient.Close();

                return result;
            }
            catch (Exception)
            {
                return -1;
            }
            
        }

        /// <summary>
        ///     Get all movies for a given show.
        /// </summary>
        /// <param name="showId">The ID of the show.</param>
        /// <returns>A generic list of movies.</returns>
        public List<Movie> GetMoviesForShow(int showId)
        {
            try
            {
                string sql = "SELECT * FROM bif_movie M, bif_price_category P WHERE M.PriceCategory=P.Id AND M.Id in(SELECT Movie From bif_show_has_movie WHERE Show = @showId) ORDER BY M.Title ASC";

                SqlCommand command = DbClient.GetDbCommand(sql);
                command.Parameters.AddWithValue("@showId", showId);

                IDataReader reader = command.ExecuteReader();
                Movie mov;
                List<Movie> movies = new List<Movie>();
                while (reader.Read())
                {
                    mov = new Movie();

                    mov.Id = (int)reader["Id"];
                    mov.Title = reader["Title"].ToString();
                    mov.Year = (int)reader["Year"];
                    mov.Duration = (int)reader["Duration"];
                    mov.Actors = reader["Actors"].ToString();
                    mov.Directors = reader["Directors"].ToString();
                    mov.Summary = reader["Summary"].ToString();
                    mov.PriceCategory = new PriceCategory((int)reader["PriceCategory"], (string)reader["Name"], (decimal)reader["Price"]);

                    movies.Add(mov);
                }
                reader.Close();
                DbClient.Close();
                if (movies.Count > 0)
                {
                    return movies;
                }
                return null;
            }
            catch (Exception)
            {
                return null;
            }            
        }

        /// <summary>
        ///     Get all movies currently showing.
        /// </summary>
        /// <returns>A generic list of movies.</returns>
        public List<Movie> GetCurrentMovies()
        {
            try
            {
                IShowDb sdb = new ShowDb();
                List<Show> currentShows = sdb.GetCurrentShows();
                List<Movie> movies = new List<Movie>();

                foreach (Show s in currentShows)
                {
                    foreach (Movie m in s.Movies)
                    {
                        Movie nm = movies.Find(e => e.Id == m.Id);
                        if (nm == null)
                            movies.Add(m);
                    }
                }

                if (movies.Count > 0)
                {
                    return movies;
                }
                return null;
            }
            catch (Exception)
            {
                return null;
            }
        }

        /// <summary>
        ///     Gets a list of movies, given certain parameters.
        /// </summary>
        /// <param name="searchPara">Valid parameters: Title, Actors, Directors & Description</param>
        /// <param name="queryString">The actual search string.</param>
        /// <returns>A generic list of movies.</returns>
        public List<Movie> SearchMovies(ICollection<string> searchPara, string queryString)
        {
            string[] legalPara = { "Title", "Actors", "Directors", "Summary" };

            if (searchPara.Count == 0)
            {
                throw new EmptyParameterException();
            }

            foreach (var item in searchPara)
            {
                if (!legalPara.Contains(item))
                {
                    throw new IllegalParameterException();
                }
            }

            string sql = "SELECT * FROM bif_movie, bif_price_category P WHERE (PriceCategory=P.Id) AND ( ";
            List<string> paraList = searchPara.ToList();

            for (int i = 0; i < searchPara.Count; i++)
            {
                if (i > 0)
                {
                    sql = sql + " OR ";
                }
                sql = sql + paraList[i] + " LIKE " + "@queryString";
            }

            sql += ")";

            SqlCommand command = DbClient.GetDbCommand(sql);

            command.Parameters.AddWithValue("@queryString", "%" + queryString + "%");

            Movie mov;
            List<Movie> movies = new List<Movie>();

            using (IDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    mov = new Movie();

                    mov.Id = (int)reader["Id"];
                    mov.Title = reader["Title"].ToString();
                    mov.Year = (int)reader["Year"];
                    mov.Duration = (int)reader["Duration"];
                    mov.Actors = reader["Actors"].ToString();
                    mov.Directors = reader["Directors"].ToString();
                    mov.Summary = reader["Summary"].ToString();
                    mov.PriceCategory = new PriceCategory((int)reader["PriceCategory"], (string)reader["Name"], (decimal)reader["Price"]);

                    movies.Add(mov);
                }
            }
            return movies;
        }
    }

    public class IllegalParameterException : Exception
    {

    }

    public class EmptyParameterException : Exception
    {

    }
}
